﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad

Public Class ClienteDatos

    Dim Path As String = "H:\DATOS\PRUEBA\TABLAS\"

    'Obtiene todos los datos del cliente X Codigo
    Function obtenerClienteXID(codCliente As String, flagActivo As String) As Cliente
        Dim entCliente As Cliente = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT c.CLI_Codigo, GRC_Codigo, CLI_Descripcion, CLI_Ruc, CLI_Linea, CLI_Dni, CLI_DireccionFiscal, cv.VEN_Codigo, " &
                                  "CLI_NomComercial, CLI_Telefono, CLI_Marca, CLI_Percepcion, CLI_Latitud, CLI_Longitud, " &
                                  "DIS_Codigo, COB_Codigo, PRC_Codigo, CLC_Codigo, TIC_Codigo, ESC_Codigo, CAC_Codigo, CNV_Codigo, CLI_Activo, " &
                                  "CLI_EmpRazonSocial, CLI_EmpTelefono, CLI_EmpFax, CLI_EmpEmail, " &
                                  "CLI_MDocVencidos, CLI_MLetAceptar, CLI_MLetProtestadas, CLI_MSinLinCredito, CLI_MBloqManual, MBL_Codigo " &
                                  "FROM Cliente c " &
                                  "INNER JOIN ClienteVendedor cv " &
                                  "ON cv.CLI_Codigo = c.CLI_Codigo AND cv.CLV_Activo = '1' " &
                                  "WHERE c.CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
            End With
            Dim ds As New DataSet
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                entCliente = New Cliente(ds.Tables(0).Rows(0)("CLI_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("GRC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Descripcion").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Ruc").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_Linea").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Dni").ToString.Trim, ds.Tables(0).Rows(0)("CLI_DireccionFiscal").ToString.Trim, ds.Tables(0).Rows(0)("VEN_Codigo").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_NomComercial").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Telefono").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Marca").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_Percepcion").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Latitud").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Longitud").ToString.Trim, ds.Tables(0).Rows(0)("DIS_Codigo").ToString.Trim,
                                         ds.Tables(0).Rows(0)("COB_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("PRC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TIC_Codigo").ToString.Trim,
                                         ds.Tables(0).Rows(0)("ESC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CAC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CNV_Codigo").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_EmpRazonSocial").ToString.Trim, ds.Tables(0).Rows(0)("CLI_EmpTelefono").ToString.Trim, ds.Tables(0).Rows(0)("CLI_EmpFax").ToString.Trim, ds.Tables(0).Rows(0)("CLI_EmpEmail").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_MDocVencidos").ToString.Trim, ds.Tables(0).Rows(0)("CLI_MLetAceptar").ToString.Trim, ds.Tables(0).Rows(0)("CLI_MLetProtestadas").ToString.Trim, ds.Tables(0).Rows(0)("CLI_MSinLinCredito").ToString.Trim,
                                         ds.Tables(0).Rows(0)("CLI_MBloqManual").ToString.Trim, ds.Tables(0).Rows(0)("MBL_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Activo").ToString.Trim)
            End If
        Catch e As Exception
            entCliente = Nothing
        Finally
            coneccion.Close()
        End Try

        Return entCliente
    End Function

    'Obtiene a los clientes de un grupo
    Function obtenerClientesVinculados(codCliente As String) As DataSet
        Dim sConn As String
        Dim sqlComm As OdbcDataAdapter
        Dim ds As New Data.DataSet()

        Dim query As String = "SELECT a.CODIGO, a.NOM1 " &
                              "FROM ARCLIENT a " &
                              "WHERE a.CODMAT = (SELECT ar.CODMAT FROM ARCLIENT ar " &
                              "                  WHERE ar.CODIGO = '" & codCliente & "') " &
                              "AND a.CODIGO <> '" & codCliente & "'"

        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "ARCLIENT.dbf") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sqlComm = New OdbcDataAdapter(query, dbConn)
                sqlComm.Fill(ds)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Return ds
    End Function

    'Obtiene a los clientes segun parametro enviado
    Function obtenerClientesXFiltro(codCliente As String, ruc As String, descrip As String,
                                    codVendedor As String, disCodigo As String, proCodigo As String,
                                    depCodigo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT c.CLI_Codigo,GRC_Codigo,RTRIM(c.CLI_Descripcion) AS CliDescripcion, " &
                                  "CLI_NomComercial,CLI_DireccionFiscal,COB_Codigo,c.DIS_Codigo,CLI_Latitud,CLI_Longitud, " &
                                  "(CASE WHEN c.CLI_DNI IS NULL THEN c.CLI_Ruc ELSE c.CLI_DNI END) AS Identidad, " &
                                  "c.CLI_Telefono, RTRIM(v.VEN_Descripcion) As VenDescripcion, " &
                                  "PRC_Codigo,CLC_Codigo,TIC_Codigo,ESC_Codigo,CAC_Codigo, CNV_Codigo, CLI_DNI " &
                                  "FROM Cliente c " &
                                  "INNER JOIN ClienteVendedor cv " &
                                  "ON cv.CLi_Codigo = c.CLI_Codigo AND CLV_Activo = '1' " &
                                  "LEFT JOIN Vendedor v " &
                                  "ON v.VEN_Codigo = cv.VEN_Codigo AND v.VEN_Activo = '1' " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.DIS_Codigo = c.DIS_Codigo " & IIf(disCodigo = "0", "", " AND d.DIS_Codigo = '" & disCodigo & "'") & " AND d.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo " & IIf(proCodigo = "0", "", " AND p.PRO_Codigo = '" & proCodigo & "'") & " AND p.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo " & IIf(depCodigo = "0", "", " AND de.DEP_Codigo = '" & depCodigo & "'") & " AND de.DEP_Activo = '1' " &
                                  " WHERE c.CLI_Activo = '1' AND c.CLI_Codigo NOT IN ('00-000','00-001','00-002')"

            If codCliente <> "" Then
                query = query & " AND c.CLI_Codigo = '" & codCliente & "' "
            End If
            If descrip <> "" Then
                query = query & " AND c.CLI_Descripcion LIKE '%" & descrip & "%' "
            End If
            If ruc <> "" Then
                query = query & " AND (c.CLI_Ruc = '" & ruc & "' OR c.CLI_DNI = '" & ruc & "') "
            End If
            If codVendedor <> "" Then
                query = query & " AND RTRIM(c.VEN_Codigo) = '" & codVendedor & "' "
            End If

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtiene al cliente por el numero de RUC XML
    Function obtenerClienteSunat(numeroRUC As String) As Cliente
        Dim cliente As Cliente = Nothing

        Dim celda
        On Error Resume Next
        Dim web = "http://www.sunat.gob.pe/w/wapS01Alias?ruc=" & numeroRUC
        Dim XML = CreateObject("Microsoft.XMLHTTP")
        XML.Open("POST", web, False)
        XML.Send()
        Dim principio
        Dim final
        Dim posicion1
        Dim posicion2
        Dim dato

        cliente = New Cliente(numeroRUC, textoLimpio(obtenerValorSunat(XML, numeroRUC + " - ", "<br/></small>")), textoLimpio(obtenerValorSunat(XML, "Antiguo Ruc.</b><br/></small>" & vbCrLf & "	 		" & vbCrLf & "     		<small>", "</small>" & vbCrLf & "	 	<br/>")),
                              textoLimpio(obtenerValorSunat(XML, "Estado.</b>", "</small><br/>" & vbCrLf)), textoLimpio(obtenerValorSunat(XML, "<br/>" & vbCrLf & "    " & vbCrLf & "      " & vbCrLf & "      <small><strong>", "</small>" & vbCrLf & "      <br/>" & vbCrLf & "    " & vbCrLf & "      <small><strong>Agente")),
                              textoLimpio(obtenerValorSunat(XML, "<small><strong>Agente Retenci&#xF3;n IGV.</strong>" & vbCrLf & "      " & vbCrLf & "        	" & vbCrLf & "            <strong>", "" & vbCrLf & "    	  " & vbCrLf & "    	<br/>" & vbCrLf & "    </small>" & vbCrLf & "	  <small><b>Nombre")),
                              textoLimpio(obtenerValorSunat(XML, "Nombre Comercial.</b><br/>", "</small><br/>" & vbCrLf & "      <small><b>Direcci&#xF3;n.</b>")),
                              textoLimpio(obtenerValorSunat(XML, "Direcci&#xF3;n.</b><br/>", "</small><br/>" & vbCrLf & "      <small>Situaci&#xF3;n.<b>")),
                              textoLimpio(obtenerValorSunat(XML, "Situaci&#xF3;n.<b> ", "</b></small><br/>" & vbCrLf & "      <small><b>Tel&#xE9;fono(s).</b>")),
                              textoLimpio(obtenerValorSunat(XML, "Tel&#xE9;fono(s).</b><br/>", "</small><br/>" & vbCrLf & "      <small><b>Dependencia.</b><br/>")),
                              textoLimpio(obtenerValorSunat(XML, "Dependencia.</b><br/> ", "</small><br/>" & vbCrLf & "	  <small><b>Tipo.</b><br/>")),
                              textoLimpio(obtenerValorSunat(XML, "Tipo.</b><br/> ", "</small><br/>" & vbCrLf & "	  " & vbCrLf & "	     ")),
                              textoLimpio(obtenerValorSunat(XML, "Inicio Act.</b><br/>", "</small><br/>" & vbCrLf & "      <small><b>Act. Econ&#xF3;mica.")),
                              textoLimpio(obtenerValorSunat(XML, "Act. Econ&#xF3;mica. </b> ", "</small><br/>" & vbCrLf & "	  -->")),
                              textoLimpio(obtenerValorSunat(XML, "DNI</b> : ", "</small><br/>" & vbCrLf & "           <small><b>Fecha")),
                              textoLimpio(obtenerValorSunat(XML, "Fecha Nacimiento.</b> ", "</small><br/>" & vbCrLf & "         " & vbCrLf & "	  <!--")))

        XML = Nothing

        Return cliente
    End Function

    'Funcion que realiza los cortes para el metodo anterior
    Private Function obtenerValorSunat(XML As Object, cadenaInicio As String, cadenaFinal As String) As String
        Dim cadenaResultante As String = ""

        Dim posicion1 = InStr(XML.responseText, cadenaInicio)
        Dim posicion2 = InStr(XML.responseText, cadenaFinal)

        If Not (posicion1 = 0 Or posicion2 = 0) Then
            cadenaResultante = Mid(XML.responseText, posicion1 + cadenaInicio.Length, (posicion2 - posicion1) - cadenaInicio.Length)
        End If

        Return cadenaResultante
    End Function

    'Funcion que devuelve el texto del corte limpio fuera de caracteres especiales
    Private Function textoLimpio(texto As String) As String
        Dim resultado As String = ""

        resultado = texto.Replace("&amp;", "&").Replace("</strong>", " ").Replace("&#209;", "Ñ").Replace("&#xFA;", "ú").Replace("&#xF3;", "ó").Replace("&#xE9;", "é")

        Return resultado
    End Function

    'Obtienes las procedencias de los clientes
    Function obtenerProcedenciaCliente() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT PRC_Codigo, PRC_Descripcion " &
                                  "FROM ProcedenciaCliente " &
                                  "WHERE PRC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("PRC_Codigo") = 0
            row.Item("PRC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtienes las clases de los clientes
    Function obtenerClaseCliente() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT CLC_Codigo, CLC_Descripcion " &
                                  "FROM ClaseCliente " &
                                  "WHERE CLC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("CLC_Codigo") = 0
            row.Item("CLC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtienes los tipos de entrega
    Function obtenerTipoEntrega() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT TEN_Codigo, TEN_Descripcion " &
                                  "FROM TipoEntrega " &
                                  "WHERE TEN_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("TEN_Codigo") = 0
            row.Item("TEN_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtiene el estado de los clientes
    Function obtenerEstadoCliente() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT ESC_Codigo, ESC_Descripcion " &
                                  "FROM EstadoCliente " &
                                  "WHERE ESC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("ESC_Codigo") = 0
            row.Item("ESC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'obtiene las categorias de los clientes
    Function obtenerCategoriaCliente() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT CAC_Codigo, CAC_Descripcion " &
                                  "FROM CategoriaCliente " &
                                  "WHERE CAC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("CAC_Codigo") = 0
            row.Item("CAC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtiene los tipos de clientes X la Clase
    Function obtenerTipoClienteXClase(codClase As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT TIC_Codigo, TIC_Descripcion " &
                                  "FROM TipoCliente " &
                                  "WHERE TIC_Activo = '1' " &
                                  "AND CLC_Codigo = @CLC"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLC", codClase)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("TIC_Codigo") = 0
            row.Item("TIC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'registra al cliente
    Function registrarCliente(cliente As Cliente, usuario As String) As Boolean
        Dim valor As Boolean = False
        Dim query As String = ""
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Dim comand As SqlCommand = coneccion.CreateCommand()
        Dim transaction As SqlTransaction

        transaction = coneccion.BeginTransaction("SampleTransaction")
        comand.Connection = coneccion
        comand.Transaction = transaction
        Try
            query = "INSERT INTO Cliente(CLI_Codigo,GRC_Codigo,CLI_Descripcion,CLI_Ruc,CLI_Linea,CLI_Dni,CLI_DireccionFiscal,CLI_NomComercial, " &
                    "CLI_Telefono,CLI_Marca,CLI_Percepcion,CLI_Latitud,CLI_Longitud,DIS_Codigo,COB_Codigo,PRC_Codigo,CLC_Codigo,TIC_Codigo,ESC_Codigo,CAC_Codigo,CNV_Codigo,CLI_EmpRazonSocial,CLI_EmpTelefono,CLI_EmpFax,CLI_EmpEmail,CLI_MDocVencidos,CLI_MLetAceptar,CLI_MLetProtestadas,CLI_MSinLinCredito,CLI_MBloqManual,MBL_Codigo,CLI_Activo) " &
                    "VALUES(@CLI,@GRC,@DES,@RUC,@LIN,@DNI,@DIR,@NCO,@TEL,@MAR,@PER,@LAT,@LON,@DIS,@COB,@PRC,@CLC,@TIC,@ESC,@CAC,@CNV,@CERS,@CETE,@CEFA,@CEEM,@BL1,@BL2,@BL3,@BL4,@BL5,@MBL,@ACT)"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@GRC", cliente.GRC_Cliente)
                .Parameters.AddWithValue("@DES", cliente.CLI_RazonSocial)
                .Parameters.AddWithValue("@RUC", cliente.CLI_RUC)
                .Parameters.AddWithValue("@LIN", cliente.CLI_LineaCredito)
                .Parameters.AddWithValue("@DNI", cliente.CLI_DNI)
                .Parameters.AddWithValue("@DIR", cliente.CLI_DireccionFiscal)
                .Parameters.AddWithValue("@NCO", cliente.CLI_NomComercial)
                .Parameters.AddWithValue("@TEL", cliente.CLI_Telefono)
                .Parameters.AddWithValue("@MAR", cliente.CLI_Marca)
                .Parameters.AddWithValue("@PER", cliente.CLI_Percepcion)
                .Parameters.AddWithValue("@LAT", cliente.CLI_Latitud)
                .Parameters.AddWithValue("@LON", cliente.CLI_Longitud)
                .Parameters.AddWithValue("@DIS", cliente.DIS_Codigo)
                .Parameters.AddWithValue("@COB", cliente.COB_Codigo)
                .Parameters.AddWithValue("@PRC", cliente.PRC_Codigo)
                .Parameters.AddWithValue("@CLC", cliente.CLC_Codigo)
                .Parameters.AddWithValue("@TIC", cliente.TIC_Codigo)
                .Parameters.AddWithValue("@ESC", cliente.ESC_Codigo)
                .Parameters.AddWithValue("@CAC", cliente.CAC_Codigo)
                .Parameters.AddWithValue("@CERS", cliente.CLI_EmpRazonSocial)
                .Parameters.AddWithValue("@CETE", cliente.CLI_EmpTelefono)
                .Parameters.AddWithValue("@CEFA", cliente.CLI_EmpFax)
                .Parameters.AddWithValue("@CEEM", cliente.CLI_EmpEmail)
                .Parameters.AddWithValue("@CNV", cliente.CNV_Codigo)
                .Parameters.AddWithValue("@ACT", cliente.CLI_Activo)
                .Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .Parameters.AddWithValue("@BL1", cliente.CLI_MDocumentosVencidos)
                .Parameters.AddWithValue("@BL2", cliente.CLI_MLetrasAceptar)
                .Parameters.AddWithValue("@BL3", cliente.CLI_MLetrasProtestadas)
                .Parameters.AddWithValue("@BL4", cliente.CLI_MSinLineaCredito)
                .Parameters.AddWithValue("@BL5", cliente.CLI_MBloqueoManual)
                .Parameters.AddWithValue("@MBL", IIf(cliente.MBL_Codigo = "0", DBNull.Value, cliente.MBL_Codigo))
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO GrupoCliente " &
                    "VALUES(@GRC,'No Definido', '1')"

            With comand
                .CommandText = query
                '.Parameters.AddWithValue("@GRC", cliente.GRC_Cliente)
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO ClienteVendedor(CIA,CLI_Codigo,VEN_Codigo,CLV_FechaAsignacion,CLV_FechaDesactivacion,CLV_Activo) " &
                    "VALUES(@CIA,@CLI,@VEN,GETDATE(),NULL,'1')"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@VEN", cliente.VEN_Codigo)
                .Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .ExecuteNonQuery()
            End With

            query = "DELETE CondicionVentaCliente " &
                    "WHERE CLI_Codigo = @CLI AND CIA = @CIA"

            With comand
                .CommandText = query
                '.Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .ExecuteNonQuery()
            End With

            If cliente.CNV_Codigo = "C" Then
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta " &
                        "WHERE CNV_Codigo = @CNV"
            ElseIf cliente.CNV_Codigo = "L" Then
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta " &
                        "WHERE CNV_Codigo = @CNV OR CNV_Codigo = 'C'"
            Else
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta "
            End If

            With comand
                .CommandText = query
                '.Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                'If cliente.CNV_Codigo <> "F" Then
                '    .Parameters.AddWithValue("@CNV", cliente.CNV_Codigo)
                'End If
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO ClienteLineaCredito(CLI_Codigo,CLC_MontoActual,CLC_NuevoMonto,CLC_FechaIngreso,PER_Codigo,CLC_Activo) " &
                    "VALUES(@CLI,0.0,0.0,GETDATE(),@PER,'01','1')"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@PER", usuario)
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO ClienteConcepto(CLI_Codigo,CON_Codigo,CCN_Fecha,CCN_FechaModf,CCN_Descripcion,PER_Codigo,CCN_Activo) " &
                    "VALUES(@CLI,1,GETDATE(),GETDATE(),'Se registro al cliente con linea de credito S/. 0.00',@PER,'1')"

            With comand
                .CommandText = query
                .ExecuteNonQuery()
            End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'actualiza al cliente
    Function actualizarCliente(cliente As Cliente) As Boolean
        Dim valor As Boolean = False
        'Dim comando As SqlCommand = Nothing
        Dim query As String = ""
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Dim comand As SqlCommand = coneccion.CreateCommand()
        Dim transaction As SqlTransaction

        transaction = coneccion.BeginTransaction("SampleTransaction")
        comand.Connection = coneccion
        comand.Transaction = transaction
        Try
            query = "UPDATE Cliente " &
                    "SET GRC_Codigo = @GRC, CLI_Descripcion = @DES, CLI_Ruc = @RUC, " &
                    "CLI_Dni = @DNI, CLI_DireccionFiscal = @DIR, CLI_NomComercial = @NCO, " &
                    "CLI_Telefono = @TEL, CLI_Marca = @MAR, " &
                    "CLI_Percepcion = @PER, CLI_Latitud = @LAT, CLI_Longitud = @LON, " &
                    "DIS_Codigo = @DIS, COB_Codigo = @COB, PRC_Codigo = @PRC, CLC_Codigo = @CLC, " &
                    "TIC_Codigo = @TIC, ESC_Codigo = @ESC, CAC_Codigo = @CAC, CNV_Codigo = @CNV, CLI_Activo = @ACT, " &
                    "CLI_EmpRazonSocial = @CERS, CLI_EmpTelefono = @CETE, CLI_EmpFax = @CEFA, CLI_EmpEmail = @CEEM, " &
                    "CLI_MDocVencidos = @BL1, CLI_MLetAceptar = @BL2, CLI_MLetProtestadas = @BL3, CLI_MSinLinCredito = @BL4, CLI_MBloqManual = @BL5, MBL_Codigo = @MBL " &
                    "WHERE CLI_Codigo = @CLI"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@GRC", cliente.GRC_Cliente)
                .Parameters.AddWithValue("@DES", cliente.CLI_RazonSocial)
                .Parameters.AddWithValue("@RUC", cliente.CLI_RUC)
                '.Parameters.AddWithValue("@LIN", cliente.CLI_LineaCredito)
                .Parameters.AddWithValue("@DNI", cliente.CLI_DNI)
                .Parameters.AddWithValue("@DIR", cliente.CLI_DireccionFiscal)
                .Parameters.AddWithValue("@NCO", cliente.CLI_NomComercial)
                .Parameters.AddWithValue("@TEL", cliente.CLI_Telefono)
                .Parameters.AddWithValue("@MAR", cliente.CLI_Marca)
                .Parameters.AddWithValue("@PER", cliente.CLI_Percepcion)
                .Parameters.AddWithValue("@LAT", cliente.CLI_Latitud)
                .Parameters.AddWithValue("@LON", cliente.CLI_Longitud)
                .Parameters.AddWithValue("@DIS", cliente.DIS_Codigo)
                .Parameters.AddWithValue("@COB", cliente.COB_Codigo)
                .Parameters.AddWithValue("@PRC", cliente.PRC_Codigo)
                .Parameters.AddWithValue("@CLC", cliente.CLC_Codigo)
                .Parameters.AddWithValue("@TIC", cliente.TIC_Codigo)
                .Parameters.AddWithValue("@ESC", cliente.ESC_Codigo)
                .Parameters.AddWithValue("@CAC", cliente.CAC_Codigo)
                .Parameters.AddWithValue("@CNV", cliente.CNV_Codigo)
                .Parameters.AddWithValue("@ACT", cliente.CLI_Activo)
                .Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .Parameters.AddWithValue("@CERS", cliente.CLI_EmpRazonSocial)
                .Parameters.AddWithValue("@CETE", cliente.CLI_EmpTelefono)
                .Parameters.AddWithValue("@CEFA", cliente.CLI_EmpFax)
                .Parameters.AddWithValue("@CEEM", cliente.CLI_EmpEmail)
                .Parameters.AddWithValue("@BL1", cliente.CLI_MDocumentosVencidos)
                .Parameters.AddWithValue("@BL2", cliente.CLI_MLetrasAceptar)
                .Parameters.AddWithValue("@BL3", cliente.CLI_MLetrasProtestadas)
                .Parameters.AddWithValue("@BL4", cliente.CLI_MSinLineaCredito)
                .Parameters.AddWithValue("@BL5", cliente.CLI_MBloqueoManual)
                .Parameters.AddWithValue("@MBL", IIf(cliente.MBL_Codigo = "0", DBNull.Value, cliente.MBL_Codigo))
                .ExecuteNonQuery()
            End With

            query = "UPDATE ClienteVendedor " &
                    "SET VEN_Codigo = @VEN, CLV_FechaAsignacion = GETDATE() " &
                    "WHERE CIA = @CIA AND CLI_Codigo = @CLI " &
                    "AND CLV_Activo = '1'"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@VEN", cliente.VEN_Codigo)
                .Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .ExecuteNonQuery()
            End With

            query = "DELETE CondicionVentaCliente " &
                    "WHERE CLI_Codigo = @CLI AND CIA = @CIA"

            With comand
                .CommandText = query
                '.Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                .ExecuteNonQuery()
            End With

            If cliente.CNV_Codigo = "C" Then
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta " &
                        "WHERE CNV_Codigo = @CNV"
            ElseIf cliente.CNV_Codigo = "L" Then
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta " &
                        "WHERE CNV_Codigo = @CNV OR CNV_Codigo = 'C'"
            Else
                query = "INSERT INTO CondicionVentaCliente " &
                        "SELECT @CIA, @CLI, CNV_Codigo, '1' " &
                        "FROM CondicionVenta "
            End If

            With comand
                .CommandText = query
                '.Parameters.AddWithValue("@CIA", "01")
                '.Parameters.AddWithValue("@CLI", cliente.CLI_Codigo)
                'If cliente.CNV_Codigo <> "F" Then
                '    .Parameters.AddWithValue("@CNV", cliente.CNV_Codigo)
                'End If
                .ExecuteNonQuery()
            End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'obtenerGruposRegistrados
    Function obtenerGruposClientes() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT GRC_Codigo " &
                                  "FROM GrupoCliente " &
                                  "WHERE GRC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'obtener datos del propietario
    Function obtenerClientePropietarioXID(codCliPropietario As String) As ClientePropietario
        Dim entClientePropietario As ClientePropietario = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT CLP_Codigo, CLP_Dni, CLP_Descripcion, CLP_Domicilio, CLP_Fax, CLP_Telefono, ISNULL(DIS_Codigo,'0') AS Distrito, ISNULL(CAR_Codigo,'0') AS Cargo, CLI_Codigo, TIP_Codigo, CLP_Activo " &
                                  "FROM ClientePersonas " &
                                  "WHERE CLP_Codigo = @CLP"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLP", codCliPropietario)
            End With
            Dim ds As New DataSet
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                entClientePropietario = New ClientePropietario(ds.Tables(0).Rows(0)("CLP_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLP_Dni").ToString.Trim, ds.Tables(0).Rows(0)("CLP_Descripcion").ToString.Trim, ds.Tables(0).Rows(0)("CLP_Domicilio").ToString.Trim, ds.Tables(0).Rows(0)("CLP_Fax").ToString.Trim,
                                                               ds.Tables(0).Rows(0)("CLP_Telefono").ToString.Trim, ds.Tables(0).Rows(0)("Distrito").ToString.Trim, ds.Tables(0).Rows(0)("Cargo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TIP_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLP_Activo").ToString.Trim)
            End If
        Catch e As Exception
            entClientePropietario = Nothing
        Finally
            coneccion.Close()
        End Try

        Return entClientePropietario
    End Function

    'Obtener Tipos personas del cliente
    Function obtenerClientePersonas(codCliente As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT cp.CLP_Codigo, cp.CLP_Dni, cp.CLP_Descripcion, cp.CLP_Domicilio, cp.CLP_Fax, cp.CLP_Telefono, de.DEP_Descripcion, " &
                                  "p.PRO_Descripcion, d.DIS_Descripcion, cp.DIS_Codigo, c.CAR_Descripcion, cp.CAR_Codigo, cp.CLP_Activo,  tp.TIP_Descripcion, " &
                                  "cp.TIP_Codigo " &
                                  "FROM ClientePersonas cp " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.DIS_Codigo = cp.DIS_Codigo AND d.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo AND p.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo AND de.DEP_Activo = '1' " &
                                  "INNER JOIN Cargo c " &
                                  "ON c.CAR_Codigo = cp.CAR_Codigo AND c.CAR_Activo = '1' " &
                                  "INNER JOIN TipoPersona tp " &
                                  "ON tp.TIP_Codigo = cp.TIP_Codigo AND tp.TIP_Activo = '1' " &
                                  "WHERE cp.CLI_Codigo = @CLI " &
                                  "ORDER BY CLP_Activo DESC, CLP_Codigo ASC"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'obtener Tipos de personas Cliente
    Function obtenerTiposClientePersonas() As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT TIP_Codigo, TIP_Descripcion " &
                                  "FROM TipoPersona " &
                                  "WHERE TIP_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("TIP_Codigo") = 0
            row.Item("TIP_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Registramos las personas del CLiente
    Function registrarClientePropietario(cliPropietario As ClientePropietario) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "INSERT INTO ClientePersonas(CLP_Dni,CLP_Descripcion,CLP_Domicilio,CLP_Fax,CLP_Telefono,DIS_Codigo,CAR_Codigo,CLI_Codigo,TIP_Codigo,CLP_Activo) " &
                                  "VALUES(@DNIP,@DESP,@DOMP,@FAXP,@TELP,@DISP,@CARP,@CLI,@TIP,@ACTP)"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DNIP", IIf(cliPropietario.CLP_Dni = "", DBNull.Value, cliPropietario.CLP_Dni))
                .Parameters.AddWithValue("@DESP", IIf(cliPropietario.CLP_Descripcion = "", DBNull.Value, cliPropietario.CLP_Descripcion))
                .Parameters.AddWithValue("@DOMP", IIf(cliPropietario.CLP_Domicilio = "", DBNull.Value, cliPropietario.CLP_Domicilio))
                .Parameters.AddWithValue("@FAXP", IIf(cliPropietario.CLP_Fax = "", DBNull.Value, cliPropietario.CLP_Fax))
                .Parameters.AddWithValue("@TELP", IIf(cliPropietario.CLP_Telefono = "", DBNull.Value, cliPropietario.CLP_Telefono))
                .Parameters.AddWithValue("@DISP", IIf(cliPropietario.DIS_Codigo = "0", DBNull.Value, cliPropietario.DIS_Codigo))
                .Parameters.AddWithValue("@CARP", IIf(cliPropietario.CAR_Codigo = "0", DBNull.Value, cliPropietario.CAR_Codigo))
                .Parameters.AddWithValue("@CLI", cliPropietario.CLI_Codigo)
                .Parameters.AddWithValue("@TIP", IIf(cliPropietario.TIP_Codigo = "0", DBNull.Value, cliPropietario.TIP_Codigo))
                .Parameters.AddWithValue("@ACTP", cliPropietario.CLP_Activo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Actualiza los datos de las personas
    Function actualizarClientePropietario(cliPropietario As ClientePropietario) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "UPDATE ClientePersonas " &
                                  "SET CLP_Dni = @DNIP, CLP_Descripcion = @DESP, " &
                                  "CLP_Domicilio = @DOMP, CLP_Fax = @FAXP, CLP_Telefono = @TELP, " &
                                  "DIS_Codigo = @DISP, CAR_Codigo = @CARP, CLP_Activo = @ACTP, TIP_Codigo = @TIP " &
                                  "WHERE CLI_Codigo = @CLI AND CLP_Codigo = @CLP"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DNIP", IIf(cliPropietario.CLP_Dni = "", DBNull.Value, cliPropietario.CLP_Dni))
                .Parameters.AddWithValue("@DESP", IIf(cliPropietario.CLP_Descripcion = "", DBNull.Value, cliPropietario.CLP_Descripcion))
                .Parameters.AddWithValue("@DOMP", IIf(cliPropietario.CLP_Domicilio = "", DBNull.Value, cliPropietario.CLP_Domicilio))
                .Parameters.AddWithValue("@FAXP", IIf(cliPropietario.CLP_Fax = "", DBNull.Value, cliPropietario.CLP_Fax))
                .Parameters.AddWithValue("@TELP", IIf(cliPropietario.CLP_Telefono = "", DBNull.Value, cliPropietario.CLP_Telefono))
                .Parameters.AddWithValue("@DISP", IIf(cliPropietario.DIS_Codigo = "0", DBNull.Value, cliPropietario.DIS_Codigo))
                .Parameters.AddWithValue("@CARP", IIf(cliPropietario.CAR_Codigo = "0", DBNull.Value, cliPropietario.CAR_Codigo))
                .Parameters.AddWithValue("@CLI", cliPropietario.CLI_Codigo)
                .Parameters.AddWithValue("@TIP", IIf(cliPropietario.TIP_Codigo = "0", DBNull.Value, cliPropietario.TIP_Codigo))
                .Parameters.AddWithValue("@ACTP", cliPropietario.CLP_Activo)
                .Parameters.AddWithValue("@CLP", cliPropietario.CLP_Codigo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Obtener clientes Vinculados
    Function obtenerClientesVinculados(codGrupo As String, codCliente As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT CLI_Codigo, CLI_Ruc, CLI_Descripcion, CLI_Activo " &
                                  "FROM Cliente " &
                                  "WHERE GRC_Codigo = @GRC AND CLI_Codigo <> @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
                .Parameters.AddWithValue("@GRC", codGrupo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Registramos los celulares de la empresa
    Function registrarCelularesEmpresa(eceCodigo As String, eceOperador As String, eceTipo As String, eceNumero As String, cliCodigo As String, eceActivo As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "INSERT INTO EmpresaCelulares(ECE_Operador,ECE_Tipo,ECE_Numero,CLI_Codigo,ECE_Activo) " &
                                  "VALUES(@OPE,@TIP,@NUM,@CLI,@ACT)"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@OPE", eceOperador)
                .Parameters.AddWithValue("@TIP", eceTipo)
                .Parameters.AddWithValue("@NUM", eceNumero)
                .Parameters.AddWithValue("@CLI", cliCodigo)
                .Parameters.AddWithValue("@ACT", eceActivo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Actualizamos los celulares de la empresa
    Function actualizarCelularesEmpresa(eceCodigo As String, eceOperador As String, eceTipo As String, eceNumero As String, cliCodigo As String, eceActivo As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "UPDATE EmpresaCelulares " &
                                  "SET ECE_Operador = @OPE, ECE_Tipo = @TIP, " &
                                  "ECE_Numero = @NUM, ECE_Activo = @ACT " &
                                  "WHERE ECE_Codigo = @ECE AND CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@OPE", eceOperador)
                .Parameters.AddWithValue("@TIP", eceTipo)
                .Parameters.AddWithValue("@NUM", eceNumero)
                .Parameters.AddWithValue("@CLI", cliCodigo)
                .Parameters.AddWithValue("@ACT", eceActivo)
                .Parameters.AddWithValue("@ECE", eceCodigo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'obtener celulares de la empresa
    Function obtenerEmpresaCelulares(codCliente As String) As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT ECE_Codigo, ECE_Operador, ECE_Tipo, ECE_Numero, CLI_Codigo, ECE_Activo " &
                                  "FROM EmpresaCelulares " &
                                  "WHERE CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'obtener las direcciones segun los tipos
    Function obtenerDireccionesCliente(cia As String, codCliente As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT CIA, CLEN_Codigo, CLI_Codigo, CLEN_Direccion, CLEN_Referencia, " &
                                  "CLEN_Contacto, d.DIS_Codigo, CLEN_Latitud, CLEN_Longitud, de.DEP_Descripcion, " &
                                  "p.PRO_Descripcion, d.DIS_Descripcion, te.TEN_Descripcion, te.TEN_Codigo, CLEN_Activo " &
                                  "FROM ClienteEntrega ce " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo AND p.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo AND de.DEP_Activo = '1' " &
                                  "INNER JOIN TipoEntrega te " &
                                  "ON te.TEN_Codigo = ce.TEN_Codigo AND te.TEN_Activo = '1' " &
                                  "WHERE CLI_Codigo = @CLI AND CIA = @CIA " &
                                  "ORDER BY te.TEN_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
                .Parameters.AddWithValue("@CIA", cia)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Registramos la direccion de entrega
    Function registrarClienteEntrega(clienteEntrega As ClienteEntrega) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "INSERT INTO ClienteEntrega(CIA,CLI_Codigo,CLEN_Direccion,CLEN_Referencia, " &
                                  "CLEN_Contacto,DIS_Codigo,CLEN_Latitud,CLEN_Longitud,TEN_Codigo,CLEN_Activo) " &
                                  "VALUES(@CIA,@CLI,@DIR,@REF,@CON,@DIS,@LAT,@LON,@TEN,@ACT)"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CIA", clienteEntrega.CIA)
                .Parameters.AddWithValue("@CLI", clienteEntrega.CLI_Codigo)
                .Parameters.AddWithValue("@DIR", clienteEntrega.CLEN_Direccion)
                .Parameters.AddWithValue("@REF", clienteEntrega.CLEN_Referencia)
                .Parameters.AddWithValue("@CON", clienteEntrega.CLEN_Contacto)
                .Parameters.AddWithValue("@DIS", clienteEntrega.DIS_Codigo)
                .Parameters.AddWithValue("@LAT", clienteEntrega.CLEN_Latitud)
                .Parameters.AddWithValue("@LON", clienteEntrega.CLEN_Longitud)
                .Parameters.AddWithValue("@TEN", clienteEntrega.TEN_Codigo)
                .Parameters.AddWithValue("@ACT", clienteEntrega.CLEN_Activo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Registramos la direccion de entrega
    Function actualizarClienteEntrega(clienteEntrega As ClienteEntrega) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "UPDATE ClienteEntrega " &
                                  "SET CLEN_Direccion = @DIR, CLEN_Referencia = @REF, CLEN_Contacto = @CON, " &
                                  "DIS_Codigo = @DIS, CLEN_Latitud = @LAT, CLEN_Longitud = @LON, TEN_Codigo = @TEN, CLEN_Activo = @ACT " &
                                  "WHERE CIA = @CIA AND CLEN_Codigo = @COD AND CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CIA", clienteEntrega.CIA)
                .Parameters.AddWithValue("@CLI", clienteEntrega.CLI_Codigo)
                .Parameters.AddWithValue("@DIR", clienteEntrega.CLEN_Direccion)
                .Parameters.AddWithValue("@REF", clienteEntrega.CLEN_Referencia)
                .Parameters.AddWithValue("@CON", clienteEntrega.CLEN_Contacto)
                .Parameters.AddWithValue("@DIS", clienteEntrega.DIS_Codigo)
                .Parameters.AddWithValue("@LAT", clienteEntrega.CLEN_Latitud)
                .Parameters.AddWithValue("@LON", clienteEntrega.CLEN_Longitud)
                .Parameters.AddWithValue("@COD", clienteEntrega.CLEN_Codigo)
                .Parameters.AddWithValue("@TEN", clienteEntrega.TEN_Codigo)
                .Parameters.AddWithValue("@ACT", clienteEntrega.CLEN_Activo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'obtener datos de la direccion de entrega
    Function obtenerDireccionEntregaXID(codEntrega As String) As ClienteEntrega
        Dim entClienteEntrega As ClienteEntrega = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT CIA,CLEN_Codigo,CLI_Codigo,CLEN_Direccion,CLEN_Referencia, " &
                                  "CLEN_Contacto, DIS_Codigo, CLEN_Latitud, CLEN_Longitud, TEN_Codigo, CLEN_Activo " &
                                  "FROM ClienteEntrega " &
                                  "WHERE CLEN_Codigo = @COD"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@COD", codEntrega)
            End With
            Dim ds As New DataSet
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                entClienteEntrega = New ClienteEntrega(ds.Tables(0).Rows(0)("CIA").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Codigo").ToString.Trim,
                                                       ds.Tables(0).Rows(0)("CLEN_Direccion").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Referencia").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Contacto").ToString.Trim,
                                                       ds.Tables(0).Rows(0)("DIS_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Latitud").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Longitud").ToString.Trim,
                                                       ds.Tables(0).Rows(0)("TEN_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Activo").ToString.Trim)
            End If
        Catch e As Exception
            entClienteEntrega = Nothing
        Finally
            coneccion.Close()
        End Try

        Return entClienteEntrega
    End Function

    'obtener agencias Sucursales del cliente
    Function obtenerSucursalesXCliente(codCliente As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "SELECT cat.ATS_Codigo, at.AGE_Descripcion, ats.ATS_Descripcion, ats.ATS_Direccion, " &
                                  "de.DEP_Descripcion, p.PRO_Descripcion, d.DIS_Descripcion, cat.CAT_Activo " &
                                  "FROM ClienteAgenciaTransporteSucursal cat " &
                                  "INNER JOIN AgenciaTransporteSucursal ats " &
                                  "ON ats.ATS_Codigo = cat.ATS_Codigo AND ats.ATS_Activo = '1' " &
                                  "INNER JOIN AgenciaTransporte at " &
                                  "ON at.AGE_Codigo = ats.AGE_Codigo AND at.AGE_Activo = '1' " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.DIS_Codigo = ats.DIS_Codigo AND d.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo AND p.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo AND de.DEP_Activo = '1' " &
                                  "WHERE cat.CLI_Codigo = @CLI AND cat.CAT_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'desactivar sucursal al cliente
    Function actualizaSucursalXCliente(codSucursal As String, codCliente As String, flag As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "UPDATE ClienteAgenciaTransporteSucursal " &
                                  "SET CAT_Activo = @ACT " &
                                  "WHERE CLI_Codigo = @CLI AND ATS_Codigo = @ATS"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@ACT", flag)
                .Parameters.AddWithValue("@CLI", codCliente)
                .Parameters.AddWithValue("@ATS", codSucursal)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Obtener motivos de bloqueos
    Function obtenerMotivosBloqueos() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT MBL_Codigo, MBL_Descripcion " &
                                  "FROM MotivoBloqueo " &
                                  "WHERE MBL_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("MBL_Codigo") = 0
            row.Item("MBL_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtener motivos de linea de crédito
    Function obtenerMotivosLineaCredito() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT MLC_Codigo, MLC_Descripcion " &
                                  "FROM MotivoLineaCredito " &
                                  "WHERE MLC_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("MLC_Codigo") = 0
            row.Item("MLC_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Actualizar la linea de credito del cliente
    Function actualizarLineaCreditoXCliente(cliCodigo As String, codMotivo As String, montoNuevo As Decimal, montoActual As Decimal, usuario As String) As Boolean
        Dim valor As Boolean = False
        Dim query As String = ""
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Dim comand As SqlCommand = coneccion.CreateCommand()
        Dim transaction As SqlTransaction

        transaction = coneccion.BeginTransaction("SampleTransaction")
        comand.Connection = coneccion
        comand.Transaction = transaction
        Try
            query = "UPDATE Cliente " &
                    "SET CLI_Linea = @LIN " &
                    "WHERE CLI_Codigo = @CLI"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@CLI", cliCodigo)
                .Parameters.AddWithValue("@LIN", montoNuevo)
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO ClienteLineaCredito(CLI_Codigo,CLC_MontoActual,CLC_NuevoMonto,MLC_Codigo,CLC_FechaIngreso,PER_Codigo,CLC_Activo) " &
                    "VALUES(@CLI,@MOA,@NUM,@MLC,GETDATE(),@PER,'1')"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@MOA", montoActual)
                .Parameters.AddWithValue("@NUM", montoNuevo)
                .Parameters.AddWithValue("@MLC", codMotivo)
                .Parameters.AddWithValue("@PER", usuario)
                .ExecuteNonQuery()
            End With

            query = "INSERT INTO ClienteConcepto(CLI_Codigo,CON_Codigo,CCN_Fecha,CCN_FechaModf,CCN_Descripcion,PER_Codigo,CCN_Activo) " &
                    "VALUES(@CLI,3,GETDATE(),GETDATE(),'Se actualizo la linea de credito del cliente Actual(S/.) : " & montoActual & " a Nuevo(S/.) : " & montoNuevo & "',@PER,'1')"

            With comand
                .CommandText = query
                .ExecuteNonQuery()
            End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Obtener conceptos del cliente
    Function obtenerConceptosXCliente(codCliente As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT c.CON_Descripcion, CONVERT(VARCHAR(15),cc.CCN_Fecha,103) AS Fecha, " &
                                  "cc.CCN_Descripcion, pp.PER_Nombres + ' ' + pp.PER_Apellidos AS Usuario, cc.CCN_Codigo, c.CON_Codigo " &
                                  "FROM ClienteConcepto cc " &
                                  "INNER JOIN Concepto c " &
                                  "ON c.CON_Codigo = cc.CON_Codigo AND c.CON_Activo = '1' " &
                                  "INNER JOIN PersonaPerfil pp " &
                                  "ON pp.PER_Codigo = cc.PER_Codigo AND pp.PER_Activo = '1' " &
                                  "WHERE cc.CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", codCliente)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Obtienes los tipos de conceptos
    Function obtenerTipoConceptos() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT CON_Codigo, CON_Descripcion " &
                                  "FROM Concepto " &
                                  "WHERE CON_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("CON_Codigo") = 0
            row.Item("CON_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    'Registramos el concepto del cliente
    Function registrarClienteConcepto(cliCodigo As String, codConcepto As String, descripcion As String, usuario As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "INSERT INTO ClienteConcepto(CLI_Codigo,CON_Codigo,CCN_Fecha,CCN_FechaModf,CCN_Descripcion,PER_Codigo,CCN_Activo) " &
                                  "VALUES(@CLI,@CON,GETDATE(),GETDATE(),@DES,@PER,'1')"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CLI", cliCodigo)
                .Parameters.AddWithValue("@CON", codConcepto)
                .Parameters.AddWithValue("@DES", descripcion)
                .Parameters.AddWithValue("@PER", usuario)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    'Actualizamos el concepto del cliente
    Function actualizartClienteConcepto(cliCodigo As String, codConcepto As String, descripcion As String, usuario As String, codRegistro As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Try
            Dim query As String = "UPDATE ClienteConcepto " &
                                  "SET CON_Codigo = @CON, CCN_FechaModf = GETDATE(), " &
                                  "CCN_Descripcion = @DES, PER_Codigo = @PER " &
                                  "WHERE CCN_Codigo = @CCN AND CLI_Codigo = @CLI"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CON", codConcepto)
                .Parameters.AddWithValue("@DES", descripcion)
                .Parameters.AddWithValue("@PER", usuario)
                .Parameters.AddWithValue("@CCN", codRegistro)
                .Parameters.AddWithValue("@CLI", cliCodigo)
                .ExecuteNonQuery()
            End With
            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

End Class
